﻿CREATE PROCEDURE [dbo].[pr_OverviewPositionByMonth] (@date date)
AS
BEGIN
	select r.Name, 
	(select count(1) from Position ps inner join goc gs on ps.GocAttribute = gs.GocAttribute
	inner join [dbo].[Rule] rs on gs.RuleId = rs.Id where ps.Date = @date and ps.PositionStatusId in (1, 4, 6) and rs.Name = r.Name) as Budgeted,
	(select count(1) from Position ps inner join goc gs on ps.GocAttribute = gs.GocAttribute
	inner join [dbo].[Rule] rs on gs.RuleId = rs.Id where ps.Date = @date and ps.PositionStatusId = 2 and rs.Name = r.Name) as OpenPosition,
	(select count(1) from Position ps inner join goc gs on ps.GocAttribute = gs.GocAttribute
	inner join [dbo].[Rule] rs on gs.RuleId = rs.Id where ps.Date = @date and ps.PositionStatusId = 3 and rs.Name = r.Name) as OutPosition,
	(select count(1) from Position ps inner join goc gs on ps.GocAttribute = gs.GocAttribute
	inner join [dbo].[Rule] rs on gs.RuleId = rs.Id where ps.Date = @date and ps.PositionStatusId = 5 and rs.Name = r.Name) as UnBudgeted
	from Position p 
	inner join goc g  on
	p.GocAttribute = g.GocAttribute
	inner join [dbo].[Rule] r on
	g.RuleId = r.Id
	group by r.Name
	order by r.Name
END